"""
Case Type   : 基础功能-SQL语法-DDL-partition
Case Name   : Hash分区表执行rename partition,并行对该分区select
Create At   : 2022/05/20
Owner       : kyeleze
Description :
    1.创建测试表并插入数据
    2.建立db1,开始事务rename partition;建立db2,启动线程开始事务同分区select
    3.等待2秒,查看数据库连接状态,db2第一个事务加锁导致处于busy状态
    4.db1执行rollback;查看db2状态,db2执行rollback
Expect      :
    1.成功
    2.成功
    3.db2连接处于busy状态
    4.db1执行rollback;查看db2状态,db2执行rollback
History     :
"""
import os
import unittest
import time

import py_opengauss
from yat.test import Node

from testcase.utils.Logger import Logger
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.ComThread import ComThread


class HashPartitionCase(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info(f"-----{os.path.basename(__file__)} start-----")
        self.user_node = Node('PrimaryDbUser')
        self.primary_sh = CommonSH('PrimaryDbUser')
        self.constant = Constant()
        self.table_name1 = 't_hash_table_0111_01'
        self.table_name2 = 't_hash_table_0111_02'
        self.user_param = f'-U {self.user_node.db_user} ' \
                          f'-W {self.user_node.db_password}'
        self.con_info = f'opengauss://' \
                        f'{self.user_node.db_user}:' \
                        f'{self.user_node.db_password}@' \
                        f'{self.user_node.db_host}:' \
                        f'{self.user_node.db_port}/' \
                        f'{self.user_node.db_name}'
        self.log.info(self.con_info)

    def test_partition_ddl_dml(self):
        step = '--step1:创建测试表并插入数据 expect:成功--'
        self.log.info(step)
        sql_cmd = f'''drop table if exists {self.table_name1};
                   create table {self.table_name1} (col1 int, col2 int)
                   partition by hash(col1)
                   (
                   partition p1,
                   partition p2
                   );
                   insert into {self.table_name1} values(1, 1);
                   insert into {self.table_name1} values(3, 3);
                   drop table if exists {self.table_name2};
                   create table {self.table_name2} (col1 int, col2 int);
                   insert into {self.table_name2} values(3, 3);
                   '''
        result = self.primary_sh.execut_db_sql(sql_cmd,
                                               sql_type=self.user_param)
        self.log.info(result)
        self.assertIn(self.constant.CREATE_TABLE_SUCCESS,
                      result, "执行失败:" + step)

        step = '--step2:建立db1,开始事务rename partition;' \
               '建立db2,启动线程开始事务同分区select' \
               'expect:成功--'
        self.log.info(step)
        self.db1 = py_opengauss.open(self.con_info)
        begin_result = self.db1.query('begin;')
        self.log.info(begin_result)
        self.assertIn('BEGIN', begin_result, "执行失败:" + step)
        self.db2 = py_opengauss.open(self.con_info)
        begin_result = self.db2.query('begin;')
        self.log.info(begin_result)
        self.assertIn('BEGIN', begin_result, "执行失败:" + step)
        ddl_sql = f'alter table {self.table_name1} rename partition p1 to p3;'
        result = self.db1.query(ddl_sql)
        self.log.info(result)
        self.assertIn(self.constant.ALTER_TABLE_MSG, result, "执行失败:" + step)
        dml_sql = f'merge into {self.table_name1} t ' \
                  f'using {self.table_name2} t1 ' \
                  f'on (t.col1 = t1.col1) ' \
                  f'when matched then ' \
                  f'update set t.col2 = t1.col2 where t.col1 != 1 ' \
                  f'when not matched then ' \
                  f'insert values (t1.col1,t1.col2) where t1.col2 = 3;'
        thread = ComThread(self.db2.query, args=(dml_sql,))
        thread.setDaemon(True)
        thread.start()

        step = '--step3:等待2秒,查看数据库连接状态,' \
               '第二个事务因第一个事务加锁导致处于busy状态 ' \
               'expect:db2连接处于busy状态--'
        self.log.info(step)
        time.sleep(2)
        db1_state = self.db1.state
        db2_state = self.db2.state
        self.log.info(db1_state)
        self.log.info(db2_state)
        self.assertIn('idle in block', db1_state, '执行失败' + step)
        self.assertIn('busy', db2_state, '执行失败' + step)

        step = '--step4:db1执行rollback;查看db2状态,db2执行rollback ' \
               'expect:db1执行rollback;查看db2状态,db2执行rollback--'
        self.log.info(step)
        result = self.db1.query('rollback')
        self.log.info(result)
        self.assertIn('ROLLBACK', result, '执行失败' + step)
        time.sleep(1)
        db2_state = self.db2.state
        self.log.info(db2_state)
        self.assertIn('idle in block', db2_state, '执行失败' + step)
        result = self.db2.query('rollback')
        self.log.info(result)
        self.assertIn('ROLLBACK', result, '执行失败' + step)

    def tearDown(self):
        step = '--恢复环境,关闭session,删除测试表--'
        self.log.info(step)
        result = self.primary_sh.execut_db_sql(
            f'drop table {self.table_name1}, {self.table_name2};')
        self.log.info(result)
        self.assertIn(self.constant.DROP_TABLE_SUCCESS,
                      result, "执行失败:" + step)
        self.db1.close()
        self.db2.close()
        self.log.info(f'-----{os.path.basename(__file__)} end-----')
